﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms

Partial Class Report_RP01
    Inherits System.Web.UI.Page

    Dim Report As Object
    Public ScriptText As String
    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        'If Session("fundcenterS") = "" Then
        '    Session("fundcenterS") = "0011"
        '    Session("fundcenterE") = "0012"

        'End If

        'If Session("users") = "" Then
        '    Session("users") = "wassana"
        'End If
        'Session("year") = 2556
        'Session("version") = 2

        Getdata()
    End Sub


    Function Getdata() As Decimal
        Dim adapter As New Data_ReportTableAdapters.R01TableAdapter
        Dim sql As String
        Dim dt As New Data_Report.R01DataTable
        Dim dt2 As New Data_Report.R01_2DataTable

        'sql = " with cte as ("
        'sql += "  select Id, FundCenter_FundCenterCode, FundCenter_PlanYear , OrgLevel, "
        'sql += "  Description, ParentFundCenterHierarchy_Id, "
        'sql += "  cast(row_number()over(partition by ParentFundCenterHierarchy_Id order by Description) as varchar(max)) as [grouppath],"
        'sql += "  Cast(FundCenter_FundCenterCode + ' ' + Description as nvarchar(max)) as [indentspace]"
        'sql += "  from FundCenterHierarchies"
        'sql += "   where ParentFundCenterHierarchy_Id Is null And FundCenterGroup = 1"
        'sql += "  union all"
        'sql += "  select t.Id, t.FundCenter_FundCenterCode, t.FundCenter_PlanYear, t.OrgLevel,"
        'sql += "  t.Description, t.ParentFundCenterHierarchy_Id,"
        'sql += "  [grouppath] + cast(row_number()over(partition by t.ParentFundCenterHierarchy_Id order by t.FundCenter_FundCenterCode asc) as varchar(max)),"
        'sql += "  Cast(SPACE((t.Level-1) * 5) as nvarchar(max))"
        'sql += "   from cte"
        'sql += "  join FundCenterHierarchies t on cte.Id = t.ParentFundCenterHierarchy_Id"
        'sql += "  )"

        'sql += "  select case TargetVersion when 1 then [indentspace] + cte.FundCenter_FundCenterCode + ' ' + f.Description else [indentspace] + cte.Description END as FundCenter,"
        'sql += "  cte.OrgLevel"
        'sql += "  ,case TargetVersion when 1 then CASE ISNULL(ReleaseFlag,'') WHEN 'Y' THEN case ForceFlag WHEN 'Y' THEN N'Force Copy' WHEN 'N' THEN CASE LastVersionFlag WHEN 'Y' THEN N'ส่งข้อมูลแล้ว' WHEN 'N' THEN 'Copy Version แล้ว ' END ELSE CASE LastVersionFlag WHEN 'Y' THEN N'ส่งข้อมูลแล้ว' WHEN 'N' THEN N'Copy Version แล้ว ' END END ELSE CASE cte.OrgLevel WHEN 50 THEN NULL ELSE N'ยังไม่ส่งข้อมูล' END  END END AS Version1 "
        'sql += "  ,case TargetVersion when 2 then CASE ISNULL(ReleaseFlag,'') WHEN 'Y' THEN case ForceFlag WHEN 'Y' THEN N'Force Copy' WHEN 'N' THEN CASE LastVersionFlag WHEN 'Y' THEN N'ส่งข้อมูลแล้ว' WHEN 'N' THEN 'Copy Version แล้ว ' END ELSE CASE LastVersionFlag WHEN 'Y' THEN N'ส่งข้อมูลแล้ว' WHEN 'N' THEN N'Copy Version แล้ว ' END END ELSE CASE cte.OrgLevel WHEN 50 THEN NULL ELSE N'ยังไม่ส่งข้อมูล' END  END END AS  Version2 "
        'sql += "  ,case TargetVersion when 3 then CASE ISNULL(ReleaseFlag,'') WHEN 'Y' THEN case ForceFlag WHEN 'Y' THEN N'Force Copy' WHEN 'N' THEN CASE LastVersionFlag WHEN 'Y' THEN N'ส่งข้อมูลแล้ว' WHEN 'N' THEN 'Copy Version แล้ว ' END ELSE CASE LastVersionFlag WHEN 'Y' THEN N'ส่งข้อมูลแล้ว' WHEN 'N' THEN N'Copy Version แล้ว ' END END ELSE CASE cte.OrgLevel WHEN 50 THEN NULL ELSE N'ยังไม่ส่งข้อมูล' END  END END AS  Version3 "
        'sql += "  ,case TargetVersion when 4 then CASE ISNULL(ReleaseFlag,'') WHEN 'Y' THEN case ForceFlag WHEN 'Y' THEN N'Force Copy' WHEN 'N' THEN CASE LastVersionFlag WHEN 'Y' THEN N'ส่งข้อมูลแล้ว' WHEN 'N' THEN 'Copy Version แล้ว ' END ELSE CASE LastVersionFlag WHEN 'Y' THEN N'ส่งข้อมูลแล้ว' WHEN 'N' THEN N'Copy Version แล้ว ' END END ELSE CASE cte.OrgLevel WHEN 50 THEN NULL ELSE N'ยังไม่ส่งข้อมูล' END  END END AS  Version4 "

        'sql += "  from cte"
        'sql += "  full outer join ( "
        'sql += "  select FundCenter_FundCenterCode, s.OrgLevel, 1 as TargetVersion from FundCenterHierarchies s"
        'sql += "  union all"
        'sql += "  select FundCenter_FundCenterCode, s.OrgLevel, "
        'sql += "  	case OrgLevel WHEN 10 THEN 1 when 11 then 1 when 20 then 2 when 30 then 3 when 31 then 3 when 40 then 4 when 50 then 5 ELSE 0 END as TargetVersion"
        'sql += "  from FundCenterHierarchies s where s.OrgLevel > 11"

        'sql += "  ) h on h.FundCenter_FundCenterCode = cte.FundCenter_FundCenterCode"
        'sql += "  left join FundCenterReleases fcr on fcr.FundCenter_FundCenterCode = cte.FundCenter_FundCenterCode"
        'sql += "  and fcr.FundCenter_PlanYear = cte.FundCenter_PlanYear"
        'sql += "  and fcr.Version = TargetVersion"
        'sql += "  left join FundCenters f on f.FundCenterCode = cte.FundCenter_FundCenterCode"
        'sql += "  and f.PlanYear = cte.FundCenter_PlanYear"
        'sql += "   where "
        'sql += "  not cte.FundCenter_FundCenterCode = '0000'"

        'If Session("FCTRmode") = "1" Then
        '    sql += " and  cte.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        'ElseIf Session("FCTRmode") = "2" Then
        '    sql += " and  cte.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        'End If

        'If Session("VersionStatus") <> "" Then
        '    Select Case Session("VersionStatus")
        '        Case 1
        '            sql += " AND ReleaseFlag = 'Y' AND ForceFlag = 'Y' "
        '        Case 2
        '            sql += " AND ReleaseFlag = 'Y' AND LastVersionFlag = 'Y' "
        '        Case 3
        '            sql += " AND ReleaseFlag = 'Y' AND LastVersionFlag = 'N' "
        '        Case 4
        '            sql += " AND (ReleaseFlag <> 'Y' OR ReleaseFlag IS NULL ) "
        '    End Select
        '    'sql += "  and (cte.Version1 ='" & Session("VersionStatus") & "' or cte.Version2 ='" & Session("VersionStatus") & "' or cte.Version3 ='" & Session("VersionStatus") & "' or cte.Version4 ='" & Session("VersionStatus") & "')"
        'End If

        'sql += "  order by grouppath, TargetVersion desc"



        sql = "with cte as ( select grouppath,fch.FundCenter_FundCenterCode, fch.planyear as FundCenter_PlanYear, fch.OrgLevel, fctH.pathname, fch.Description from FctH "
        sql += " left join FundCenterHierarchies fch on fch.FundCenter_FundCenterCode = FctH.FundCenter_FundCenterCode "
        sql += " and fch.FundCenter_PlanYear = FctH.planyear "
        sql += " and fch.planyear = FctH.planyear "  'Add by Orn 04/08/2558
        sql += " where grouppath like ( select CONVERT(varchar(max), grouppath) + '%' from FctH where PlanYear = " & Session("year") & " and FundCenter_FundCenterCode = '" & Session("FundcenterAll") & "'))"

        sql += " select case TargetVersion when 1 then cte.pathname else SUBSTRING(cte.pathname,0,LEN(cte.pathname)-LEN(cte.Description)-4) + 'ภาพรวม ' + SUBSTRING(cte.pathname,LEN(cte.pathname)-LEN(cte.Description),LEN(cte.Description)+1) END as FundCenter,  cte.OrgLevel "
        sql += " , case TargetVersion when 1 then CASE ISNULL(ReleaseFlag,'') WHEN 'Y' THEN case ForceFlag when 'Y' then N'Force Copy' else N'ส่งข้อมูลแล้ว' end WHEN 'N' THEN N'ยังไม่ส่งข้อมูล' ELSE case cte.OrgLevel when 50 then NULL else N'ยังไม่ส่งข้อมูล' end END end as Version1"
        sql += " , case TargetVersion when 2 then CASE ISNULL(ReleaseFlag,'') WHEN 'Y' THEN case ForceFlag when 'Y' then N'Force Copy' else N'ส่งข้อมูลแล้ว' end WHEN 'N' THEN N'ยังไม่ส่งข้อมูล' ELSE N'ยังไม่ Copy Version' END end as Version2 "
        sql += " , case TargetVersion when 3 then CASE ISNULL(ReleaseFlag,'') WHEN 'Y' THEN case ForceFlag when 'Y' then N'Force Copy' else N'ส่งข้อมูลแล้ว' end WHEN 'N' THEN N'ยังไม่ส่งข้อมูล' ELSE N'ยังไม่ Copy Version' END end as Version3 "
        sql += " , case TargetVersion when 4 then CASE ISNULL(ReleaseFlag,'') WHEN 'Y' THEN case ForceFlag when 'Y' then N'Force Copy' else N'ส่งข้อมูลแล้ว' end WHEN 'N' THEN N'ยังไม่ส่งข้อมูล' ELSE N'ยังไม่ Copy Version' END end as Version4 "

        sql += " from cte full outer join ( select FundCenter_FundCenterCode, s.OrgLevel, 1 as TargetVersion from FundCenterHierarchies s "
        sql += " union "
        sql += " select FundCenter_FundCenterCode, s.OrgLevel, case OrgLevel WHEN 10 THEN 1 when 11 then 1 when 20 then 2 when 30 then 3 when 31 then 3 when 40 then 4 when 50 then 5 ELSE 0 END as TargetVersion from FundCenterHierarchies s where s.OrgLevel > 11 ) "
        sql += " h on h.FundCenter_FundCenterCode = cte.FundCenter_FundCenterCode "
        sql += " left join FundCenterReleases fcr on fcr.FundCenter_FundCenterCode = cte.FundCenter_FundCenterCode "
        sql += " and fcr.PlanYear = cte.FundCenter_PlanYear " 'Add by Orn 04/08/2558
        sql += " and fcr.FundCenter_PlanYear = cte.FundCenter_PlanYear and fcr.Version = TargetVersion left join FundCenters f on f.FundCenterCode = cte.FundCenter_FundCenterCode and f.PlanYear = cte.FundCenter_PlanYear "
        sql += " where not cte.FundCenter_FundCenterCode = '0000' "

        sql += " order by grouppath, TargetVersion desc "

        'Response.Write(sql)
        'Exit Function
        adapter.SearchNews(dt, sql)

        If Session("VersionStatus") <> "" Then
            Select Case Session("VersionStatus")
                Case 1
                    Dim nullcount As Integer = 0
                    For Each row As Data_Report.R01Row In dt
                        If If(row.IsNull("Version1"), "", row("Version1")) <> "Force Copy" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version2"), "", row("Version2")) <> "Force Copy" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version3"), "", row("Version3")) <> "Force Copy" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version4"), "", row("Version4")) <> "Force Copy" Then
                            nullcount += 1
                        End If
                        If nullcount < 4 Then
                            dt2.Rows.Add(row.ItemArray)
                        End If
                        nullcount = 0
                    Next
                Case 2
                    Dim nullcount As Integer = 0

                    For Each row As Data_Report.R01Row In dt
                        If If(row.IsNull("Version1"), "", row("Version1")) <> "ส่งข้อมูลแล้ว" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version2"), "", row("Version2")) <> "ส่งข้อมูลแล้ว" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version3"), "", row("Version3")) <> "ส่งข้อมูลแล้ว" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version4"), "", row("Version4")) <> "ส่งข้อมูลแล้ว" Then
                            nullcount += 1
                        End If
                        If nullcount < 4 Then
                            dt2.Rows.Add(row.ItemArray)
                        End If
                        nullcount = 0

                    Next
                Case 3
                    Dim nullcount As Integer = 0
                    For Each row As Data_Report.R01Row In dt
                        If If(row.IsNull("Version1"), "", row("Version1")) <> "Copy Version แล้ว" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version2"), "", row("Version2")) <> "Copy Version แล้ว" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version3"), "", row("Version3")) <> "Copy Version แล้ว" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version4"), "", row("Version4")) <> "Copy Version แล้ว" Then
                            nullcount += 1
                        End If
                        If nullcount < 4 Then
                            dt2.Rows.Add(row.ItemArray)
                        End If
                        nullcount = 0
                    Next
                Case 4
                    Dim nullcount As Integer = 0
                    For Each row As Data_Report.R01Row In dt
                        If If(row.IsNull("Version1"), "", row("Version1")) <> "ยังไม่ส่งข้อมูล" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version2"), "", row("Version2")) <> "ยังไม่ส่งข้อมูล" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version3"), "", row("Version3")) <> "ยังไม่ส่งข้อมูล" Then
                            nullcount += 1
                        End If
                        If If(row.IsNull("Version4"), "", row("Version4")) <> "ยังไม่ส่งข้อมูล" Then
                            nullcount += 1
                        End If
                        If nullcount < 4 Then
                            dt2.Rows.Add(row.ItemArray)
                        End If
                        nullcount = 0
                    Next
                    'Case Else
                    '    For Each row As Data_Report.R01Row In dt
                    '        dt2.Rows.Add(row.ItemArray)
                    '    Next
            End Select
        Else
            For Each row As Data_Report.R01Row In dt
                dt2.Rows.Add(row.ItemArray)
            Next
        End If

        Dim das As New DataSet
        das.DataSetName = "R01DS"
        das.Tables.Add(dt2)
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local
        'ReportViewer1.LocalReport.ReportPath = System.Environment.CurrentDirectory & "Report\R01.rdlc"
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", das.Tables(0)))
        ReportViewer1.DocumentMapCollapsed = True

        Dim UserName As String = Session("users")
        Dim years As String = Session("year")

        Dim users As String = Session("users")

        Dim p1, p2 As ReportParameter
        p1 = New ReportParameter("year", years)
        p2 = New ReportParameter("username", UserName)

        ReportViewer1.LocalReport.SetParameters(p1)
        ReportViewer1.LocalReport.SetParameters(p2)
        ReportViewer1.LocalReport.Refresh()

        'Response.Write(dt.Rows.Count & sql)
        Session("count") = dt.Rows.Count

        Return dt.Rows.Count
    End Function


    Public Function nPageNumber() As String
        Dim str As String
        str = Me.Report.Globals!PageNumber.ToString()
        Return str
    End Function

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            ScriptText = "<script> wclose();</script>"
        End If
    End Sub
End Class
